﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;

namespace ContactManager.Common
{
    class ExcelUtil
    {
        public static void saveAs(DataSet ds,string[] headers,ProgressBar pb)
        {
            try 
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "导出文件保存路径";
                saveFileDialog.ShowDialog();
                string strName = saveFileDialog.FileName;
                //打开文件对话框
                if (strName.Length != 0)
                {
                    //显示进度条
                    pb.Visible = true;
                    //设置excel对象
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;//组件null填充值
                    ApplicationClass excel = new ApplicationClass();
                    excel.Application.Workbooks.Add(true);
                    excel.Visible = false;
                    if (excel == null)
                    {
                        MessageBox.Show("Excel无法启动", "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    Workbooks books = excel.Workbooks;
                    Workbook book = books.Add(miss);
                    Worksheet sheet = (Worksheet)book.ActiveSheet;
                    sheet.Name = "test";
                    
                    for(int i = 1; i <= headers.Length; i++)
                    {
                        excel.Cells[1, i] = headers[i - 1];
                    }

                    //填充数据
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        for (int j = 1; j < ds.Tables[0].Columns.Count - 1; j++)
                        {
                            excel.Cells[i + 2, j] = ds.Tables[0].Rows[i][j];
                        }
                        pb.Value += 100 / ds.Tables[0].Rows.Count;
                    }
                    //保存文件
                    sheet.SaveAs(strName,
                                 miss,
                                 miss,
                                 miss,
                                 miss,
                                 miss,
                                 XlSaveAsAccessMode.xlNoChange,
                                 miss,
                                 miss,
                                 miss);
                    book.Close(false, miss, miss);
                    books.Close();
                    excel.Quit();
                    //释放资源
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    //gc
                    GC.Collect();
                    MessageBox.Show("数据已经成功导出", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    pb.Value = 0;
                    pb.Visible = false;
                }
            }
            catch(Exception ex) 
            {
                MessageBox.Show(ex.Message, "错误提示");
                GC.Collect();
            }
        }

        public static void saveDataGridView(DataGridView ds1, string header)
        {
            try
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "导出文件保存路径";
                saveFileDialog.ShowDialog();
                string strName = saveFileDialog.FileName;
                //打开文件对话框
                if (strName.Length != 0)
                {
                  
                    //设置excel对象
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;//组件null填充值
                    ApplicationClass excel = new ApplicationClass();
                    excel.Application.Workbooks.Add(true);
                    excel.Visible = false;
                    if (excel == null)
                    {
                        MessageBox.Show("Excel无法启动", "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    Workbooks books = excel.Workbooks;
                    Workbook book = books.Add(miss);
                    Worksheet sheet = (Worksheet)book.ActiveSheet;
                    sheet.Name = "test";


                    excel.Cells[1, 1] = header;
                    for (int i = 2; i < ds1.ColumnCount + 2; i++)
                    {
                        excel.Cells[2, i - 1] = ds1.Columns[i - 2].HeaderText;
                    }

                    //填充数据
                    for (int i = 0; i < ds1.Rows.Count; i++)
                    {
                        for (int j = 0; j < ds1.ColumnCount; j++)
                        {
                            excel.Cells[i + 3, j+1] = ds1.Rows[i].Cells[j].Value;
                        }
                    }

                    //保存文件
                    sheet.SaveAs(strName,
                                 miss,
                                 miss,
                                 miss,
                                 miss,
                                 miss,
                                 XlSaveAsAccessMode.xlNoChange,
                                 miss,
                                 miss,
                                 miss);
                    book.Close(false, miss, miss);
                    books.Close();
                    excel.Quit();
                    //释放资源
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    //gc
                    GC.Collect();
                    MessageBox.Show("数据已经成功导出", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误提示");
                GC.Collect();
            }
        }

        public static void importContacters(ProgressBar pb)
        {
            OpenFileDialog ofDialog = new OpenFileDialog();
            ofDialog.Filter = "导入Excel (*.xls)|*.xls";
            ofDialog.FilterIndex = 0;
            ofDialog.RestoreDirectory = true;
            ofDialog.Title = "导入excel联系人";
            if (ofDialog.ShowDialog() == DialogResult.OK)
            {
                string strName = ofDialog.FileName;
                DataSet ds = getXlsData(strName);
                pb.Visible = true;
                pb.Value = 0;

                int count = 0;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    string name = "";
                    if (dr[0].ToString().Length != 0)
                    {
                        name = dr[0].ToString();
                    }
                    else
                    {
                        MessageBox.Show("对不起,excel格式不对，姓名，职务，电话1为必填项目。日期格式如：2011/02/13.地址格式为:省,市,详细地址(符号均为英文半角)---已经插入" + count + "条记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    string pname = "";
                    if (dr[1].ToString().Length != 0)
                    {
                        pname = dr[1].ToString();
                    }

                    DateTime birthday = DateTime.Now;
                    if (dr[2].ToString().Length != 0)
                    {
                        birthday = Convert.ToDateTime(dr[2].ToString());
                    }

                    string company = "";
                    if (dr[3].ToString().Length != 0)
                    {
                        company = dr[3].ToString();
                    }

                    string title = "";
                    if (dr[4].ToString().Length != 0)
                    {
                        title = dr[4].ToString();
                    }
                    else
                    {
                        MessageBox.Show("对不起,excel格式不对，姓名，职务，电话1为必填项目。日期格式如：2011/02/13.地址格式为:省,市,详细地址(符号均为英文半角)---已经插入" + count + "条记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    string qq = "";
                    if (dr[5].ToString().Length != 0)
                    {
                        qq = dr[5].ToString();
                    }

                    string msn = "";
                    if (dr[6].ToString().Length != 0)
                    {
                        msn = dr[6].ToString();
                    }

                    string phone1 = "";
                    if (dr[7].ToString().Length != 0)
                    {
                        phone1 = dr[7].ToString();
                    }
                    else
                    {
                        MessageBox.Show("对不起,excel格式不对，姓名，职务，电话1为必填项目。日期格式如：2011/02/13.地址格式为:省,市,详细地址(符号均为英文半角)---已经插入" + count + "条记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    string phone2 = "";
                    if (dr[8].ToString().Length != 0)
                    {
                        phone2 = dr[8].ToString();
                    }

                    string mail = "";
                    if (dr[9].ToString().Length != 0)
                    {
                        mail = dr[9].ToString();
                    }

                    string cphone = "";
                    if (dr[10].ToString().Length != 0)
                    {
                        cphone = dr[10].ToString();
                    }

                    string url = "";
                    if (dr[11].ToString().Length != 0)
                    {
                        url = dr[11].ToString();
                    }

                    string hobby = "";
                    if (dr[12].ToString().Length != 0)
                    {
                        hobby = dr[12].ToString();
                    }

                    string address = "";
                    if (dr[13].ToString().Length != 0)
                    {
                        address = dr[13].ToString();
                    }

                    int groupId = 1;//默认分组ID--潜在客户
                    try
                    {
                        StringBuilder sb = new StringBuilder("insert into contacters(c_name,c_pname,c_company,c_title,c_qq,c_msn,c_phone_one,c_phone_second,c_email,c_company_phone,c_url,c_address,c_group_id,c_birthday) values(?,?,?,?,?,?,?,?,?,?,?,?,?,#" + birthday.ToString("yyyy/MM/dd HH:mm:ss") + "#)");
                        OleDbParameter[] parameters = {new OleDbParameter("@c_name",name),
                                               new OleDbParameter("@c_pname",pname),
                                               new OleDbParameter("@c_company",company),
                                               new OleDbParameter("@c_title",title),
                                               new OleDbParameter("@c_qq",qq),
                                               new OleDbParameter("@c_msn",msn),
                                               new OleDbParameter("@c_phone_one",phone1),
                                               new OleDbParameter("@c_phone_second",phone2),
                                               new OleDbParameter("@c_email",mail),
                                               new OleDbParameter("@c_company_phone",cphone),
                                               new OleDbParameter("@c_url",url),
                                               new OleDbParameter("@c_address",address),
                                               new OleDbParameter("@c_group_id",groupId)
                                              };
                        AccessHelper.ExecuteNonQuery(sb.ToString(), parameters);
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message, "错误,已经导入" + count + "条记录", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    //保存数据，遇到日期转换问题。
                    pb.Value += 100 / ds.Tables[0].Rows.Count;
                    count++;
                }
                MessageBox.Show("导入成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                pb.Visible = false;
            }
        }

        public static DataSet getXlsData(string fileName) 
        {
           DataSet ds = new DataSet();
            try
            {
                string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
                System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
                string strCom = "SELECT * FROM [test$]";
                Conn.Open();
                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
                myCommand.Fill(ds, "[test$]");
                Conn.Close();
                
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
           return ds;
        }

        public static void exportWorkInc(DataGridView ds1, DataGridView ds2, string sum, ProgressBar pb)
        {
            try
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "导出文件保存路径";
                saveFileDialog.ShowDialog();
                string strName = saveFileDialog.FileName;
                //打开文件对话框
                if (strName.Length != 0)
                {
                    //显示进度条
                    pb.Visible = true;
                    //设置excel对象
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;//组件null填充值
                    ApplicationClass excel = new ApplicationClass();
                    excel.Application.Workbooks.Add(true);
                    excel.Visible = false;
                    if (excel == null)
                    {
                        MessageBox.Show("Excel无法启动", "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    Workbooks books = excel.Workbooks;
                    Workbook book = books.Add(miss);
                    Worksheet sheet = (Worksheet)book.ActiveSheet;
                    sheet.Name = "test";


                    excel.Cells[1, 1] = "支出表";
                    for (int i = 2; i <= ds1.ColumnCount; i++)
                    {
                        excel.Cells[2, i - 1] = ds1.Columns[i - 1].HeaderText;
                    }

                    //填充数据
                    for (int i = 0; i < ds1.Rows.Count ; i++)
                    {
                        for (int j = 1; j < ds1.ColumnCount; j++)
                        {
                            excel.Cells[i + 3, j] =ds1.Rows[i].Cells[j].Value;
                        }
                        pb.Value += 100 / ds1.Rows.Count;
                    }
                    pb.Value = 0;

                    int dsNum = ds1.Rows.Count;
                    excel.Cells[dsNum + 4, 1] = "收入表";
                    for (int i = 2; i <= ds2.ColumnCount; i++)
                    {
                        excel.Cells[dsNum + 5, i - 1] = ds2.Columns[i - 1].HeaderText;
                    }

                    //填充数据
                    for (int i = 0; i < ds2.Rows.Count - 1; i++)
                    {
                        for (int j = 1; j < ds2.ColumnCount; j++)
                        {
                            excel.Cells[i + 6 + dsNum, j] = ds2.Rows[i].Cells[j].Value;
                        }
                        pb.Value += 100 / ds2.Rows.Count - 1;
                    }

                    int ds2Num = ds2.Rows.Count;
                    excel.Cells[6 + dsNum + ds2Num, 1] = sum;

                    //保存文件
                    sheet.SaveAs(strName,
                                 miss,
                                 miss,
                                 miss,
                                 miss,
                                 miss,
                                 XlSaveAsAccessMode.xlNoChange,
                                 miss,
                                 miss,
                                 miss);
                    book.Close(false, miss, miss);
                    books.Close();
                    excel.Quit();
                    //释放资源
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    //gc
                    GC.Collect();
                    MessageBox.Show("数据已经成功导出", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    pb.Value = 0;
                    pb.Visible = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误提示");
                GC.Collect();
            }
        }

        public static void exportYearWorkInc(DataGridView ds1, DataGridView ds2, string sum, ProgressBar pb)
        {
            try
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "导出文件保存路径";
                saveFileDialog.ShowDialog();
                string strName = saveFileDialog.FileName;
                //打开文件对话框
                if (strName.Length != 0)
                {
                    //显示进度条
                    pb.Visible = true;
                    //设置excel对象
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;//组件null填充值
                    ApplicationClass excel = new ApplicationClass();
                    excel.Application.Workbooks.Add(true);
                    excel.Visible = false;
                    if (excel == null)
                    {
                        MessageBox.Show("Excel无法启动", "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    Workbooks books = excel.Workbooks;
                    Workbook book = books.Add(miss);
                    Worksheet sheet = (Worksheet)book.ActiveSheet;
                    sheet.Name = "test";


                    excel.Cells[1, 1] = "支出表";
                    for (int i = 2; i <= ds1.ColumnCount; i++)
                    {
                        excel.Cells[2, i - 1] = ds1.Columns[i - 1].HeaderText;
                    }

                    //填充数据
                    for (int i = 0; i < ds1.Rows.Count; i++)
                    {
                        for (int j = 1; j < ds1.ColumnCount; j++)
                        {
                            excel.Cells[i + 3, j] = ds1.Rows[i].Cells[j].Value;
                        }
                        pb.Value += 100 / ds1.Rows.Count;
                    }
                    pb.Value = 0;

                    int dsNum = ds1.Rows.Count;
                    excel.Cells[dsNum + 4, 1] = "收入表";
                    for (int i = 2; i <= ds2.ColumnCount; i++)
                    {
                        excel.Cells[dsNum + 5, i - 1] = ds2.Columns[i - 1].HeaderText;
                    }

                    //填充数据
                    for (int i = 0; i < ds2.Rows.Count; i++)
                    {
                        for (int j = 1; j < ds2.ColumnCount; j++)
                        {
                            excel.Cells[i + 6 + dsNum, j] = ds2.Rows[i].Cells[j].Value;
                        }
                        pb.Value += 100 / ds2.Rows.Count - 1;
                    }

                    int ds2Num = ds2.Rows.Count;
                    excel.Cells[6 + dsNum + ds2Num, 1] = sum;

                    //保存文件
                    sheet.SaveAs(strName,
                                 miss,
                                 miss,
                                 miss,
                                 miss,
                                 miss,
                                 XlSaveAsAccessMode.xlNoChange,
                                 miss,
                                 miss,
                                 miss);
                    book.Close(false, miss, miss);
                    books.Close();
                    excel.Quit();
                    //释放资源
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    //gc
                    GC.Collect();
                    MessageBox.Show("数据已经成功导出", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    pb.Value = 0;
                    pb.Visible = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误提示");
                GC.Collect();
            }
        }
    }
}
